﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using web.data.entities;
using System.Configuration;
using Webdiyer.WebControls.Mvc;
using System.Web.UI;
using System.Linq.Expressions;
using System.Reflection;
using System.Xml.Schema;

namespace web.bussines
{
    public class product_service : base_service
    {
        public double? GetProductPriceByID(int productId)
        {
            using (var data = new baseEntities())
            {
                var pro = data.tbl_product.Where(p => p.product_id == productId).FirstOrDefault();
                if (pro != null)
                {
                    return pro.price;
                }
                else
                {
                    return 0;
                }

            }
        }

        public product GetProductByID(int productId)
        {
            using (var data = new baseEntities())
            {
                var pro = data.tbl_product.Where(p => p.product_id == productId).FirstOrDefault();
                return pro;
            }
        }

        //public int CreateProduct(product pro)
        //{
        //    using (var data = new baseEntities())
        //    {
        //        data.AddTotbl_product(pro);
        //        data.SaveChanges();

        //        return pro.product_id;
        //    }
        //}

        //public int CreateProductItem(product_item item)
        //{
        //    using (var data = new baseEntities())
        //    {
        //        data.AddTotbl_product_item(item);
        //        data.SaveChanges();

        //        return item.product_item_id;
        //    }
        //}

        /// <summary>
        /// Đệ quy list ID danh mục sản phẩm
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public StringBuilder GetListCategoryProductIdBuilder(ref StringBuilder sb, int cateId, int? languageId)
        {
            using (var data = new baseEntities())
            {
                languageId = languageId ?? 1;   //default language 1 (vi) if null language
                var cate = (from p in data.view_category_product
                            where p.category_parent == cateId && p.language_id == languageId
                            select p.category_id).ToList();
                foreach (var item in cate)
                {
                    sb.Append(item + ",");
                    if (cate.Count() > 0)
                    {
                        GetListCategoryProductIdBuilder(ref sb, item, languageId);
                    }
                }

                return sb;
            }
        }

        /// <summary>
        /// Đệ quy list ID danh mục sản phẩm for adm
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public StringBuilder GetListCategoryProductIdBuilder(ref StringBuilder sb, int cateId)
        {
            using (var data = new baseEntities())
            {
                var cate = (from p in data.view_category_product
                            where p.category_parent == cateId
                            select p.category_id).ToList();
                foreach (var item in cate)
                {
                    sb.Append(item + ",");
                    if (cate.Count() > 0)
                    {
                        GetListCategoryProductIdBuilder(ref sb, item);
                    }
                }

                return sb;
            }
        }

        public void updateStatusProductCategory(product_category cate)
        {
            using (var data = new baseEntities())
            {
                var cate_change_status = data.tbl_product_category.Where(p => p.category_id == cate.category_id).SingleOrDefault();

                cate_change_status.category_status = cate.category_status;
                data.SaveChanges();
            }
        }
        
        public int createProductCategory(product_category cate, List<product_category_item> items)
        {
            using (var data = new baseEntities())
            {
                foreach (var item in items)
                {
                    cate.tbl_product_category_item.Add(item);
                }
                data.AddTotbl_product_category(cate);
                data.SaveChanges();

                return cate.category_id;
            }
        }

        public void deleteProductCategoryItem(int category_item_id)
        {
            using (var data = new baseEntities())
            {
                var item = data.tbl_product_category_item.Where(p => p.category_item_id == category_item_id).SingleOrDefault();
                if (item != null)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }
        
        public void deleteProductCategory(int category_id)
        {
            using (var data = new baseEntities())
            {
                var em = data.tbl_product_category.Where(p => p.category_id == category_id).FirstOrDefault();

                if (em != null)
                {


                    //Xoa all category is child
                    StringBuilder sb = new StringBuilder();
                    sb = GetListCategoryProductIdBuilder(ref sb, (int)em.category_id);
                    if (sb.ToString().EndsWith(","))
                    {
                        var arrId = sb.ToString().Substring(0, sb.Length - 1).Split(',');
                        int[] arrIdInt = arrId.Select(n => Convert.ToInt32(n)).ToArray();
                        var list_child_category = data.tbl_product_category.Where(p => arrIdInt.Contains(p.category_id)).ToList();
                        var list_child_category_id = data.tbl_product_category.Where(p => arrIdInt.Contains(p.category_id)).ToList().Select(m => m.category_id).ToArray();

                        //Xoa product_category_item of list child
                        var list_item_child = data.tbl_product_category_item.Where(p => list_child_category_id.Contains((int)p.category_id)).ToList();
                        foreach (var it in list_item_child)
                        {
                            data.DeleteObject(it);
                        }

                        //xoa product_category child in ref article
                        var list_child_ref = data.tbl_product_ref_category.Where(p => list_child_category_id.Contains((int)p.category_id)).ToList();
                        foreach (var it in list_child_ref)
                        {
                            data.DeleteObject(it);
                        }

                        //xoa list child
                        foreach (var it in list_child_category)
                        {
                            data.DeleteObject(it);
                        }
                    }
                    else
                    {

                    }

                    //Xoa product_category_item of category
                    var list_item = data.tbl_product_category_item.Where(p => p.category_id == em.category_id).ToList();
                    foreach (var it in list_item)
                    {
                        data.DeleteObject(it);
                    }

                    //Xoa product_category in ref article
                    var list_ref = data.tbl_product_ref_category.Where(p => p.category_id == em.category_id).ToList();
                    foreach (var it in list_ref)
                    {
                        data.DeleteObject(it);
                    }

                    //Xoa employee object
                    data.DeleteObject(em);
                    data.SaveChanges();
                }


            }
        }

        /// <summary>
        /// get list Product Category Items for cateId
        /// </summary>
        /// <param name="category_id"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public List<web.data.entities.product_category_item> GetListProductCategoryItemsByCateID(int category_id, int? languageId)
        {
            using (var data = new baseEntities())
            {
                List<web.data.entities.product_category_item> list = new List<product_category_item>();
                if (languageId != null)
                {
                    list = data.tbl_product_category_item.Where(p => p.category_id == category_id && p.language_id == languageId).ToList();
                }
                else
                {
                    list = data.tbl_product_category_item.Where(p => p.category_id == category_id).ToList();
                }

                return list;
            }
        }

        public web.data.entities.product_category GetProductCategoryByID(int category_id)
        {
            using (var data = new baseEntities())
            {
                web.data.entities.product_category art = data.tbl_product_category.Where(p => p.category_id == category_id).SingleOrDefault();
                return art;
            }
        }

        public void editProductCategory(product_category cate, List<product_category_item> items)
        {
            using (var data = new baseEntities())
            {
                var edit_cate = data.tbl_product_category.Where(p => p.category_id == cate.category_id).SingleOrDefault();

                if (edit_cate != null)
                {
                    edit_cate.category_status = cate.category_status;
                    edit_cate.category_sort = cate.category_sort;
                    edit_cate.category_parent = cate.category_parent;
                    edit_cate.category_param = cate.category_param;
                    edit_cate.images = cate.images;
                    data.SaveChanges();
                }

                if (items.Count > 0)
                {
                    foreach (var it in items)
                    {
                        var item = data.tbl_product_category_item.Where(p => p.category_item_id == it.category_item_id).SingleOrDefault();

                        if (item != null)
                        {
                            item.category_name = it.category_name;
                            item.category_alias = it.category_alias;
                            item.category_desc = it.category_desc;
                            item.category_desc_short = it.category_desc_short;
                            item.category_id = it.category_id;
                            item.category_item_enable = it.category_item_enable;
                            item.category_meta_description = it.category_meta_description;
                            item.category_meta_keyword = it.category_meta_keyword;
                            item.category_title = it.category_title;
                            item.language_id = it.language_id;
                            data.SaveChanges();
                        }
                        else
                        {
                            it.category_id = cate.category_id;
                            data.AddTotbl_product_category_item(it);
                            data.SaveChanges();
                        }
                    }
                }
            }
        }

        public void updateStatusProduct(product pro)
        {
            using (var data = new baseEntities())
            {
                var pro_change_status = data.tbl_product.Where(p => p.product_id == pro.product_id).SingleOrDefault();

                pro_change_status.product_status = pro.product_status;
                data.SaveChanges();
            }
        }
         
        public int createProduct(product pro, List<product_item> items, List<product_ref_category> list_ref,List<product_images> list_img, List<product_tag> lstTag)
        {
            using (var data = new baseEntities())
            {
                foreach (var item in items)
                {
                    pro.tbl_product_item.Add(item);
                }
                data.AddTotbl_product(pro);
                foreach (var it in list_ref)
                {
                    pro.tbl_product_ref_category.Add(it);
                }
                //foreach (var ite in list_img)
                //{
                //    pro.tbl_product_images.Add(ite);
                //}
                data.SaveChanges();
                 
                //tag
                if (lstTag.Count > 0) {
                    foreach (var tag in lstTag) {
                        tag.product_id = pro.product_id;
                        tag_add(tag);// ins db
                    }
                }

                return pro.product_id;
            }
        }

        /// <summary>
        /// get list Product Items for product_id
        /// </summary>
        /// <param name="category_id"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public List<web.data.entities.product_item> GetListProductItemsByProductID(int product_id, int? languageId)
        {
            using (var data = new baseEntities())
            {
                List<web.data.entities.product_item> list = new List<product_item>();
                if (languageId != null)
                {
                    list = data.tbl_product_item.Where(p => p.product_id == product_id && p.language_id == languageId).ToList();
                }
                else
                {
                    list = data.tbl_product_item.Where(p => p.product_id == product_id).ToList();
                }

                return list;
            }
        }

        public List<int> GetListCategoryByProductID(int product_id)
        {
            using (var data = new baseEntities())
            {
                var list = data.tbl_product_ref_category.Where(p => p.product_id == product_id).Select(p => p.category_id ?? 0).ToList();
                return list;
            }
        }

        public void DeleteAllProductRefByProductID(int product_id)
        {
            using (var data = new baseEntities())
            {
                var list = data.tbl_product_ref_category.Where(p => p.product_id == product_id).ToList();
                foreach (var it in list)
                {
                    data.tbl_product_ref_category.DeleteObject(it);
                    data.SaveChanges();
                }
            }
        }

        public void editProduct(product pro, List<product_item> items, List<product_ref_category> list_ref, List<product_images> list_img, List<product_tag> lstTag)
        {
            using (var data = new baseEntities())
            {
                var edit_pro = data.tbl_product.Where(p => p.product_id == pro.product_id).SingleOrDefault();

                if (edit_pro != null)
                {
                    edit_pro.product_status = pro.product_status;
                    edit_pro.images = pro.images;
                    edit_pro.price = pro.price;
                    edit_pro.product_id = pro.product_id;
                    edit_pro.old_price = pro.old_price;
                    edit_pro.modify_by = pro.modify_by;
                    edit_pro.modify_date = pro.modify_date;
                    edit_pro.quantity = pro.quantity;
                    edit_pro.sales = pro.sales;
                    edit_pro.tax = pro.tax;
                    data.SaveChanges();
                }

                if (items.Count > 0)
                {
                    foreach (var it in items)
                    {
                        var item = data.tbl_product_item.Where(p => p.product_item_id == it.product_item_id).SingleOrDefault();

                        if (item != null)
                        {
                            item.product_name = it.product_name;
                            item.product_alias = it.product_alias;
                            item.product_desc = it.product_desc;
                            item.product_desc_short = it.product_desc_short;
                            item.product_features = it.product_features;
                            item.product_item_enable = it.product_item_enable;
                            item.product_meta_keyword = it.product_meta_keyword;
                            item.product_meta_description = it.product_meta_description;
                            item.product_id = it.product_id;
                            item.product_title = it.product_title;
                            item.language_id = it.language_id;
                            data.SaveChanges();
                        }
                        else
                        {
                            it.product_id = pro.product_id;
                            data.AddTotbl_product_item(it);
                            data.SaveChanges();
                        }
                    }
                }

                //if (list_img.Count > 0)
                //{
                //    foreach (var it in list_img)
                //    {
                //        var ite = data.tbl_product_images.Where(p => p.images_id == it.images_id).SingleOrDefault();

                //        if (ite != null)
                //        {
                //            ite.images = it.images;
                //            ite.images_code = it.images_code;
                //            ite.images_enable = it.images_enable;
                //            ite.images_name = it.images_name;
                //            ite.images_thumb = it.images_thumb;
                //            ite.product_id = it.product_id;
                //            data.SaveChanges();
                //        }
                //        else
                //        {
                //            it.product_id = pro.product_id;
                //            data.AddTotbl_product_images(it);
                //            data.SaveChanges();
                //        }
                //    }
                //}

                foreach (var it in list_ref)
                {
                    edit_pro.tbl_product_ref_category.Add(it);
                }
                data.SaveChanges();
                                
                //tags
                tag_delete(edit_pro.product_id);
                if (lstTag.Count > 0)
                {
                    foreach (var tag in lstTag)
                    {
                        tag_add(tag);// ins db
                    }
                }

            }
        }

        public void deleteProduct(int product_id)
        {
            using (var data = new baseEntities())
            {
                var em = data.tbl_product.Where(p => p.product_id == product_id).FirstOrDefault();

                if (em != null)
                {

                    //Xoa product_item of product
                    var list_item = data.tbl_product_item.Where(p => p.product_id == em.product_id).ToList();
                    foreach (var it in list_item)
                    {
                        data.DeleteObject(it);
                    }

                    //Xoa product_category in ref product
                    var list_ref = data.tbl_product_ref_category.Where(p => p.product_id == em.product_id).ToList();
                    foreach (var it in list_ref)
                    {
                        data.DeleteObject(it);
                    }

                    // xoa tag
                    tag_delete(product_id);

                    //Xoa employee object
                    data.DeleteObject(em);
                    data.SaveChanges();
                }


            }
        }
         
        public void deleteProductItem(int product_item_id)
        {
            using (var data = new baseEntities())
            {
                var item = data.tbl_product_item.Where(p => p.product_item_id == product_item_id).SingleOrDefault();
                if (item != null)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        #region v_product
        public v_product GetProductViewByID(int productId, int? languageId)
        {
            using (var data = new baseEntities())
            {
                var pro = data.view_product.Where(p => p.product_id == productId && p.language_id == languageId).FirstOrDefault();
                return pro;
            }
        }

        //lay danh sach nhung san pham lien quan 
        public List<v_product> GetListProductRelated(int productId, int numberProduct)
        {
            using (var data = new baseEntities())
            {

                var list_left = (from a in data.view_product
                                 join b in data.tbl_product_relate on a.product_id equals b.product_id_b
                                 where b.product_id_a == productId
                                 select a);
                var list_right = (from d in data.view_product
                                  join c in data.tbl_product_relate on d.product_id equals c.product_id_a
                                  where c.product_id_b == productId
                                  select d);

                var list_relate = list_left.ToList().Union(list_right);

                if (numberProduct == -1)
                {
                    //get all
                    return list_relate.ToList();
                }
                else {
                    return list_relate.Take(numberProduct).ToList();
                }
                
            }
        }

        //lay danh sach nhung san pham eo lien quan 
        public PagedList<v_product> GetListProductNotRelate(int productid, string text_search, int? languageId, int? cateId, int pageindex, int pagesite)
        {
            using (var data = new baseEntities())
            {
                var list_relate = GetListProductRelated(productid, -1).Select(p => p.product_id).ToList();

                // tim san pham
                IQueryable<v_product> list;
                if (cateId != null && cateId != 0)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListCategoryProductIdBuilder(ref sb, (int)cateId, languageId);
                    if (sb.ToString().EndsWith(","))
                    {
                        var arrId = sb.ToString().Substring(0, sb.Length - 1).Split(',');
                        int[] arrIdInt = arrId.Select(n => Convert.ToInt32(n)).ToArray();
                        list = (from p in data.view_product
                                join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
                                where prc.category_id == cateId && (prc.category_id == cateId || arrIdInt.Contains(prc.category_id ?? 0))
                                select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                    }
                    else
                    {
                        list = (from p in data.view_product
                                join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
                                where prc.category_id == cateId
                                select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                    }

                }
                else
                {
                    list = (from p in data.view_product
                            select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                }

                if (languageId != null)
                {
                    list = list.Where(p => p.language_id == languageId);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    text_search = text_search.ToLower();
                    list = list.Where(p => (p.product_name != null && p.product_name.ToLower().Contains(text_search)) 
                                            || (p.product_desc_short != null && p.product_desc_short.Contains(text_search)) 
                                            || (p.product_desc != null && p.product_desc.Contains(text_search)) 
                                            || (p.product_features != null && p.product_features.Contains(text_search)));
                }
                // tim san pham
                // int total = 0;
                // var list = list_p_search(text_search, languageId, cateId, out total);

                if (list_relate.Count > 0)
                {
                    list = list.Where(p => list_relate.Contains(p.product_id) == false);
                }
                list = list.Where(p => p.product_id != productid);

                return list.ToPagedList(pageindex, pagesite);
            }
        }

        /// <summary>
        /// Get list product to public site
        /// </summary>
        /// <param name="cateId"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public PagedList<v_product> GetListProductByCategoryID(int? languageId, int? cateId, int pageindex, int pagesite, string orderBy, string orderType, out int total)
        {
            using (var data = new baseEntities())
            {
                IQueryable<v_product> list;
                languageId = languageId ?? web.component.global_const.LANGUAGE_ID_DEFAULT;   //default language 1 (vi) if null language

                if (cateId != null && cateId != 0)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListCategoryProductIdBuilder(ref sb, (int)cateId, languageId);
                    if (sb.ToString().EndsWith(","))
                    {
                        var arrId = sb.ToString().Substring(0, sb.Length - 1).Split(',');
                        int[] arrIdInt = arrId.Select(n => Convert.ToInt32(n)).ToArray();
                        list = (from p in data.view_product
                                join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
                                where prc.category_id == cateId && p.language_id == languageId && (prc.category_id == cateId || arrIdInt.Contains(prc.category_id ?? 0)) && p.product_status == 1
                                select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                    }
                    else
                    {
                        list = (from p in data.view_product
                                join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
                                where prc.category_id == cateId && p.language_id == languageId && p.product_status == 1
                                select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                    }

                }
                else
                {
                    list = (from p in data.view_product
                            where p.language_id == languageId
                            select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                }

                //list = list.Distinct();
                total = list.Count();

                if (!string.IsNullOrEmpty(orderBy) && !string.IsNullOrEmpty(orderType))
                {
                    Type sortByPropType = typeof(v_product).GetProperty(orderBy).PropertyType;

                    //calling the extension method using reflection
                    list = typeof(my_extensions).GetMethod("CustomSort").MakeGenericMethod(new Type[] { typeof(v_product), sortByPropType })
                                                .Invoke(list, new object[] { list, orderBy, orderType }) as IQueryable<v_product>;
                }
                else
                {
                    //if  orderBy null set default is product_name
                    list = list.OrderByDescending(p => p.product_name);
                }

                return list.ToPagedList(pageindex, pagesite);
            }
        }

        /// <summary>
        /// Get list product to product search in public site
        /// </summary>
        /// <param name="cateId"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public PagedList<v_product> GetListProductSearch(int? languageId, string text_search, int pageindex, int pagesite, string orderBy, string orderType, out int total)
        {
            using (var data = new baseEntities())
            {
                IQueryable<v_product> list;
                languageId = languageId ?? web.component.global_const.LANGUAGE_ID_DEFAULT;   //default language 1 (vi) if null language

                list = (from p in data.view_product
                        where p.language_id == languageId && p.product_status == 1
                        select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();

                if (!string.IsNullOrEmpty(text_search))
                {
                    text_search = text_search.ToLower();
                    list = list.Where(p => (p.product_name != null && p.product_name.ToLower().Contains(text_search)) 
                                        || (p.product_desc != null && p.product_desc.ToLower().Contains(text_search)) 
                                        || (p.product_desc_short != null && p.product_desc_short.Contains(text_search)) 
                                        || (p.product_features != null && p.product_features.Contains(text_search)));
                }

                total = list.Count();

                if (!string.IsNullOrEmpty(orderBy) && !string.IsNullOrEmpty(orderType))
                {
                    Type sortByPropType = typeof(v_product).GetProperty(orderBy).PropertyType;

                    //calling the extension method using reflection
                    list = typeof(my_extensions).GetMethod("CustomSort").MakeGenericMethod(new Type[] { typeof(v_product), sortByPropType })
                                                .Invoke(list, new object[] { list, orderBy, orderType }) as IQueryable<v_product>;
                }
                else
                {
                    //if  orderBy null set default is product_name
                    list = list.OrderByDescending(p => p.product_name);
                }

                return list.ToPagedList(pageindex, pagesite);
            }
        }


        /// <summary>
        /// get list product with parameter
        /// </summary>
        /// <param name="category_param"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public List<web.data.entities.v_product> GetListProductViewByParam(string category_param, int? languageId, int? number_items)
        {
            using (var data = new baseEntities())
            {
                number_items = number_items ?? 5;
                List<v_product> list = new List<v_product>();
                if (!string.IsNullOrEmpty(category_param))
                {
                    list = (from p in data.view_product
                            where p.category_param == category_param && p.language_id == languageId
                            select p).ToList();

                    list = list.OrderByDescending(p => p.product_id).ToList();
                }
                return list.Take((int)number_items).ToList();
            }
        }

        //// tim san pham tra ve list v_product
        //private IQueryable<v_product> list_p_search(string text_search, int? languageId, int? cateId, out int total)
        //{            
        //    using (var data = new baseEntities())
        //    {
        //        IQueryable<v_product> list;  
        //        if (cateId != null && cateId != 0)
        //        {
        //            StringBuilder sb = new StringBuilder();
        //            sb = GetListCategoryProductIdBuilder(ref sb, (int)cateId, languageId);
        //            if (sb.ToString().EndsWith(","))
        //            {
        //                var arrId = sb.ToString().Substring(0, sb.Length - 1).Split(',');
        //                int[] arrIdInt = arrId.Select(n => Convert.ToInt32(n)).ToArray();
        //                list = (from p in data.view_product
        //                        join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
        //                        where prc.category_id == cateId && (prc.category_id == cateId || arrIdInt.Contains(prc.category_id ?? 0))
        //                        select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
        //            }
        //            else
        //            {
        //                list = (from p in data.view_product
        //                        join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
        //                        where prc.category_id == cateId
        //                        select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
        //            }

        //        }
        //        else
        //        {
        //            list = (from p in data.view_product
        //                    select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
        //        }

        //        if (languageId != null)
        //        {
        //            list = list.Where(p => p.language_id == languageId);
        //        }

        //        if (!string.IsNullOrEmpty(text_search))
        //        {
        //            list = list.Where(p => (p.product_name != null && p.product_name.Contains(text_search)) || (p.product_desc_short != null && p.product_desc_short.Contains(text_search)) || (p.product_desc != null && p.product_desc.Contains(text_search)) || (p.product_features != null && p.product_features.Contains(text_search)));
        //        }
        //        total = list.Count();

        //        return list;
        //    }
        //}

        /// <summary>
        /// Get list product to sitemap
        /// </summary>
        /// <param name="cateId"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public List<v_product> GetListProductByCategoryIdSiteMap(int? languageId, int? cateId)
        {
            using (var data = new baseEntities())
            {
                IQueryable<v_product> list;
                languageId = languageId ?? web.component.global_const.LANGUAGE_ID_DEFAULT;   //default language 1 (vi) if null language

                if (cateId != null && cateId != 0)
                {

                    list = (from p in data.view_product
                            join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
                            where prc.category_id == cateId && p.language_id == languageId && p.product_status == 1
                            select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                }
                else
                {
                    list = (from p in data.view_product
                            where p.language_id == languageId
                            select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                }

               

                return list.ToList();
            }
        }

        /// <summary>
        /// Get list product to admin site
        /// </summary>
        /// <param name="cateId"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public PagedList<v_product> GetListProductByCategoryIDAdm(string text_search, int? languageId, int? cateId, int pageindex, int pagesite, string orderBy, string orderType, out int total)
        {
            using (var data = new baseEntities())
            {
                IQueryable<v_product> list;
                if (cateId != null && cateId != 0)
                {
                    StringBuilder sb = new StringBuilder();
                    sb = GetListCategoryProductIdBuilder(ref sb, (int)cateId, languageId);
                    if (sb.ToString().EndsWith(","))
                    {
                        var arrId = sb.ToString().Substring(0, sb.Length - 1).Split(',');
                        int[] arrIdInt = arrId.Select(n => Convert.ToInt32(n)).ToArray();
                        list = (from p in data.view_product
                                join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
                                where prc.category_id == cateId && (prc.category_id == cateId || arrIdInt.Contains(prc.category_id ?? 0))
                                select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                    }
                    else
                    {
                        list = (from p in data.view_product
                                join prc in data.tbl_product_ref_category on p.product_id equals prc.product_id
                                where prc.category_id == cateId
                                select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                    }

                }
                else
                {
                    list = (from p in data.view_product
                            select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                }

                if (languageId != null)
                {
                    list = list.Where(p => p.language_id == languageId);
                }

                if (!string.IsNullOrEmpty(text_search))
                {
                    text_search = text_search.ToLower();
                    list = list.Where(p => (p.product_name != null && p.product_name.Contains(text_search)) 
                                        || (p.product_desc_short != null && p.product_desc_short.Contains(text_search)) 
                                        || (p.product_desc != null && p.product_desc.Contains(text_search)) 
                                        || (p.product_features != null && p.product_features.Contains(text_search)));
                }

                //tim san pham
                //IQueryable<v_product> list = list_p_search(text_search, languageId, cateId,out total);
                if (!string.IsNullOrEmpty(orderBy) && !string.IsNullOrEmpty(orderType))
                {
                    Type sortByPropType = typeof(v_product).GetProperty(orderBy).PropertyType;

                    //calling the extension method using reflection
                    list = typeof(my_extensions).GetMethod("CustomSort").MakeGenericMethod(new Type[] { typeof(v_product), sortByPropType })
                                                .Invoke(list, new object[] { list, orderBy, orderType }) as IQueryable<v_product>;
                }
                else
                {
                    //if  orderBy null set default is product_name
                    list = list.OrderByDescending(p => p.product_name);
                }

                total = list.Count();

                return list.ToPagedList(pageindex, pagesite);
            }
        }


        #endregion

        #region v_category_product
        public v_category_product GetViewProductCategoryByID(int id, int? languageId)
        {
            using (var data = new baseEntities())
            {
                return data.view_category_product.Where(p => p.category_id == id && p.language_id == languageId).FirstOrDefault();
            }
        }

        /// <summary>
        /// Get List category for public page 
        /// </summary>
        /// <param name="cateId"></param>
        /// <returns></returns>
        public List<v_category_product> GetListCategoryProductByParent(int? cateId, int? languageId)
        {
            using (var data = new baseEntities())
            {
                languageId = languageId ?? 1;   //default language 1 (vi) if null language
                List<v_category_product> list_category = null;

                if (cateId == null)
                {
                    list_category = (from p in data.view_category_product
                                     where p.category_parent == null && p.category_status == 1 && p.language_id == languageId && p.category_param == null
                                     select p).ToList();
                }
                else
                {
                    list_category = (from p in data.view_category_product
                                     where p.category_parent == cateId && p.category_status == 1 && p.language_id == languageId && p.category_param == null
                                     select p).ToList();
                }

                return list_category;
            }
        }

        /// <summary>
        /// Get List category for adm page 
        /// </summary>
        /// <param name="cateId"></param>
        /// <returns></returns>
        public List<v_category_product> GetListCategoryProductByParentAdm(int? cateId, int? languageId)
        {
            using (var data = new baseEntities())
            {
                languageId = languageId ?? 1;   //default language 1 (vi) if null language
                List<v_category_product> list_category = null;

                if (cateId == null)
                {
                    list_category = (from p in data.view_category_product
                                     where p.category_parent == null && p.language_id == languageId
                                     select p).ToList();
                }
                else
                {
                    list_category = (from p in data.view_category_product
                                     where p.category_parent == cateId && p.language_id == languageId
                                     select p).ToList();
                }

                return list_category;
            }
        }

        /// <summary>
        /// for adm page
        /// </summary>
        /// <param name="pageindex"></param>
        /// <param name="pageSize"></param>
        /// <param name="text_search"></param>
        /// <param name="parent"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public List<v_category_product> getCategoryProductBuilder(string text_search, int? parent, int? languageId)
        {
            using (var data = new baseEntities())
            {
                IQueryable<v_category_product> list = getListCategoryProductBuilder(parent, languageId).AsQueryable<v_category_product>();

                if (!string.IsNullOrEmpty(text_search))
                {
                    list = list.Where(p => (p.category_name != null && p.category_name.Contains(text_search)) || (p.category_desc_short != null && p.category_desc_short.Contains(text_search)) || (p.category_desc != null && p.category_desc.Contains(text_search)));
                }

                // list = list.OrderBy(p => p.category_sort);

                return list.ToList();
            }
        }
        /// <summary>
        /// for adm page
        /// </summary>
        /// <param name="id"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public List<v_category_product> getListCategoryProductBuilder(int? id, int? languageId)
        {
            List<v_category_product> sb = new List<v_category_product>();
            return getListProductCategoryBuilder(ref sb, id, languageId, true);
        }
        /// <summary>
        /// for adm page
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <param name="languageId"></param>
        /// <param name="root"></param>
        /// <returns></returns>
        public List<v_category_product> getListProductCategoryBuilder(ref List<v_category_product> sb, int? id, int? languageId, bool root)
        {
            List<v_category_product> list_parent = GetListCategoryProductByParentAdm(id, languageId);
            foreach (var item in list_parent)
            {
                sb.Add(item);
                //get list child of item
                List<v_category_product> list_child = GetListCategoryProductByParentAdm(item.category_id, languageId);
                if (list_child.Count() > 0)
                {
                    getListProductCategoryBuilder(ref sb, item.category_id, languageId, false);
                }

            }
            return sb;
        }

        /// <summary>
        /// for adm page
        /// </summary>
        /// <param name="id"></param>
        /// <param name="languageId"></param>
        /// <returns></returns>
        public List<v_category_product> getProductCategoryDropDownlist(int? id, int? languageId)
        {
            List<v_category_product> sb = new List<v_category_product>();
            return getProductCategoryDropDownlist(ref sb, id, languageId, true);
        }
        /// <summary>
        /// for adm page
        /// </summary>
        /// <param name="sb"></param>
        /// <param name="id"></param>
        /// <param name="languageId"></param>
        /// <param name="root"></param>
        /// <returns></returns>
        public List<v_category_product> getProductCategoryDropDownlist(ref List<v_category_product> sb, int? id, int? languageId, bool root)
        {
            List<v_category_product> list_parent = GetListCategoryProductByParentAdm(id, languageId);
            foreach (var item in list_parent)
            {
                if (!root)
                {
                    item.category_name = "--" + item.category_name; //GetViewProductCategoryByID((int)item.category_parent, languageId).category_name + ">>" + item.category_name;
                    v_category_product pa = GetViewProductCategoryByID(id ?? 0, languageId);
                    foreach (var itemStr in sb)
                    {
                        if (pa != null)
                        {
                            if (itemStr.category_id == pa.category_id)
                            {
                                if (itemStr.category_name.Contains("--"))
                                {
                                    int laststr = itemStr.category_name.LastIndexOf("--") + 2;
                                    string addStr = itemStr.category_name.Substring(0, laststr);
                                    item.category_name = addStr + item.category_name;
                                }
                            }
                        }
                    }
                }
                sb.Add(item);
                //get list child of item
                List<v_category_product> list_child = GetListCategoryProductByParentAdm(item.category_id, languageId);
                if (list_child.Count() > 0)
                {

                    getProductCategoryDropDownlist(ref sb, item.category_id, languageId, false);
                }

            }
            return sb;
        }

        public web.data.entities.v_category_product GetProductCategoryViewByID(int category_id, int? languageId)
        {
            using (var data = new baseEntities())
            {
                web.data.entities.v_category_product cate = data.view_category_product.Where(p => p.category_id == category_id && p.language_id == languageId).SingleOrDefault();
                return cate;
            }
        }
        #endregion

        #region product images
        //take number item of images by product, 
        public List<product_images> GetListImagesByProduct(int productId, int numberImg)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.tbl_product_images
                            where p.product_id == productId && p.images_enable == true
                            select p).OrderByDescending(p => p.images_id);
                return list.Take(numberImg).ToList();
            }
        }

        // list by product id
        public List<web.data.entities.product_images> GetListProductImagesByProductID(int product_id)
        {
            using (var data = new baseEntities())
            {
                var list_img = data.tbl_product_images.Where(p => p.product_id == product_id).ToList();

                return list_img;
            } 
        }

        public int product_img_add(product_images it)
        {
            using (var data = new baseEntities())
            {
                data.AddTotbl_product_images(it);
                data.SaveChanges();

                return it.images_id;
            }
        }
        //delete by id
        public void deleteProductImages(int images_id)
        {
            using (var data = new baseEntities())
            {
                var item = data.tbl_product_images.Where(p => p.images_id == images_id).SingleOrDefault();
                if (item != null)
                {
                    data.DeleteObject(item);
                    data.SaveChanges();
                }
            }
        }

        public product_images ProductImageGetById(int images_id)
        {
            using (var data = new baseEntities())
            {
                var item = data.tbl_product_images.Where(p => p.images_id == images_id).FirstOrDefault();
                return item;
            }
        }
        #endregion 
         
        #region product tag

        public bool tag_add( product_tag item) {
            try
            {
                using (var data = new baseEntities())
                {
                    data.AddTotbl_product_tag(item);
                    data.SaveChanges();

                    return true;
                }
            }
            catch //(Exception ex)
            {
                return false;
            }
        }


        public bool tag_delete(int id)
        {
            try
            {
                using (var data = new baseEntities())
                {
                    var em = data.tbl_product_tag.Where(p => p.product_id == id);

                    if (em != null)
                    {
                        foreach (var it in em)
                        {
                            data.DeleteObject(it);
                        } 
                        data.SaveChanges();
                    }
                    return true;
                }
            }
            catch //(Exception ex)
            {
                return false;
            }
        }

        //get tag by product id
        public List<product_tag> tag_getby_productid(int id)
        {
            using (var data = new baseEntities())
            {
                var list = (from dt in data.tbl_product_tag
                            where dt.product_id == id
                            select dt);
                return list.ToList();
            }
        }

        /// <summary>
        /// get list all product tag for public site
        /// </summary>
        /// <returns></returns>
        public List<web.data.entities.product_tag> GetListProductTag()
        {
            using (var data = new baseEntities())
            {
                var list = data.tbl_product_tag.Where(p => !string.IsNullOrEmpty(p.tag)).GroupBy(p => p.tag).OrderByDescending(p => p.Count()).Select(p=>p.Key).ToList().Take(30);
                var list_tag = (from p in data.tbl_product_tag
                                where list.Contains(p.tag)
                                select p).DistinctBy(p => p.tag).AsQueryable<product_tag>();
                return list_tag.ToList();
            } 
        }

        /// <summary>
        /// Get list product to public site
        /// </summary>
        /// <param name="cateId"></param>
        /// <param name="orderBy"></param>
        /// <param name="orderType"></param>
        /// <returns></returns>
        public PagedList<v_product> GetListProductByTag(int? languageId, string tag, int pageindex, int pagesite, string orderBy, string orderType, out int total)
        {
            using (var data = new baseEntities())
            {
                IQueryable<v_product> list;
                languageId = languageId ?? web.component.global_const.LANGUAGE_ID_DEFAULT;   //default language 1 (vi) if null language
                if (string.IsNullOrEmpty(tag)) { tag = ""; }

                list = (from p in data.view_product
                        join prc in data.tbl_product_tag on p.product_id equals prc.product_id
                        where prc.link == tag && p.language_id == languageId && p.product_status == 1
                        select p).DistinctBy(p => p.product_id).AsQueryable<v_product>();
                
                if (!string.IsNullOrEmpty(orderBy) && !string.IsNullOrEmpty(orderType))
                {
                    Type sortByPropType = typeof(v_product).GetProperty(orderBy).PropertyType;

                    //calling the extension method using reflection
                    list = typeof(my_extensions).GetMethod("CustomSort").MakeGenericMethod(new Type[] { typeof(v_product), sortByPropType })
                                                .Invoke(list, new object[] { list, orderBy, orderType }) as IQueryable<v_product>;
                }
                else
                {
                    //if  orderBy null set default is product_name
                    list = list.OrderByDescending(p => p.product_name);
                }

                total = list.Count();

                return list.ToPagedList(pageindex, pagesite);
            }
        }

        public web.data.entities.product_tag GetProductTagById(int? tag_id)
        {
            using (var data = new baseEntities())
            {
                var tag = data.tbl_product_tag.Where(p => p.product_tag_id==tag_id).FirstOrDefault();
                return tag;
            } 
        }

        public web.data.entities.product_tag GetTag(string tag)
        {
            using (var data = new baseEntities())
            {
                var tags = data.tbl_product_tag.Where(p => p.link == tag).FirstOrDefault();
                return tags;
            } 
        }

        /// <summary>
        /// get list all product tag for public site
        /// </summary>
        /// <returns></returns>
        public List<web.data.entities.product_tag> GetListProductTagByProductID(int product_id)
        {
            using (var data = new baseEntities())
            {
                var list_tag = data.tbl_product_tag.Where(p => (!string.IsNullOrEmpty(p.tag)) && p.product_id ==product_id).DistinctBy(p => p.tag).AsQueryable<product_tag>();
                return list_tag.ToList();
            }
        }
        #endregion

        #region product relate
        public bool product_relate_add(product_relate item)
        {
            try
            {
                using (var data = new baseEntities())
                {
                    var em = data.tbl_product_relate.Where(p =>
                                    (p.product_id_a == item.product_id_a && p.product_id_b == item.product_id_b) ||
                                    (p.product_id_a == item.product_id_b && p.product_id_b == item.product_id_a)
                                    );

                    if (em == null || em.Count() <= 0)
                    {
                        data.AddTotbl_product_relate(item);
                        data.SaveChanges();

                        return true;
                    }
                    return false;
                }
            }
            catch //(Exception ex)
            {
                return false;
            }
        }


        public bool product_relate_delete(int pid_a, int pid_b)
        {
            try
            {
                using (var data = new baseEntities())
                {
                    var em = data.tbl_product_relate.Where(p =>
                                (p.product_id_a == pid_a && p.product_id_b == pid_b) ||
                                (p.product_id_a == pid_b && p.product_id_b == pid_a)
                                );

                    if (em != null)
                    {
                        foreach (var it in em)
                        {
                            data.DeleteObject(it);
                        }
                        data.SaveChanges();
                    }
                    return true;
                }
            }
            catch //(Exception ex)
            {
                return false;
            }
        }
        #endregion

        #region product relate
        //lay product cung danh muc
        public List<v_product> ListOtherInCategory(int? languageId, int? cateId, int productId, int number)
        {
            using (var data = new baseEntities())
            {
                var list = (from p in data.view_product
                            where p.product_id != productId && p.category_id == cateId
                            orderby p.product_id descending
                            select p).Take(number);
                return list.ToList();
            }
        }
        #endregion
    }
}
